package com.tim;

import cn.hutool.core.map.MapUtil;
import com.alibaba.fastjson.JSON;
import com.tim.util.SqlAnalysisUtil;
import com.tim.util.StringAnalysisUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.builder.xml.XMLMapperBuilder;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.junit.Test;

import java.io.ByteArrayInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 变量、sql互转
 *
 */
@Slf4j
public class SqlHelper {

	/**resp转select属性*/
	@Test
	public void printSelectByResp(){
		String tableAlias = "t1";
    String path =
        "D:\\github\\pharmacy-mall\\pharmacy-mall-client\\src\\main\\java\\com\\shp\\tsfservice\\pharmacy\\mall\\client\\model\\resp\\DropDownListResp.java" +
				"";
		String code = " ";
		{ code="";
		}
		if(StringUtils.isNotBlank(path)){
			code = getClassBody(path);
		}

		List<String> list = StringAnalysisUtil.getFieldNameList(code);
		String tableAliasPre = StringUtils.isBlank(tableAlias) ? "" : tableAlias+".";
		StringBuilder builder = new StringBuilder();
		list.stream()
				.forEach(item->{
					if((item.endsWith("Desc") || item.endsWith("Name"))
							&& list.contains(item.substring(0, item.length()-4))){
						return;
					}
					String lower = item.toLowerCase();
					if(lower.contains("date")
							|| lower.contains("time")
							|| lower.contains("gmt")){
						String fieldName = StringAnalysisUtil.back2Subline(item)
								.replaceAll("_str$", "");
						item = String.format("\t%s%s %s,\n",
								tableAliasPre,
								fieldName,
								item);
					}else{
						item = "\t"+tableAliasPre + StringAnalysisUtil.back2Subline(item)+" "+SqlAnalysisUtil.quoteKeyword(item)+",\n";
					}
					builder.append(item);
				});
		System.out.println(builder.substring(0, builder.length()-2));
	}


	/**req转动态sql if*/
	@Test
	public void printApiCodeByReq(){
		String doName = "LesseeShopInfoDO";
		String path =
				"D:\\github\\pharmacy-mall\\pharmacy-mall-client\\src\\main\\java\\com\\shp\\tsfservice\\pharmacy\\mall\\client\\model\\req\\lessee\\shop\\LesseeShopListReq.java" +
						"";
		String code = "";
		{ code="";
		}
		if(StringUtils.isNotBlank(path)){
			code = getClassBody(path);
		}


		String[] lines = code.split("\n");
		System.out.println(String.format("LambdaQueryWrapper<%1$s> wrapper = Wrappers.<%1$s>lambdaQuery()", doName));
		System.out.println(String.format(".eq(%1$s::getYn, YesOrNoEnum.YES.getCode().toString())", doName));
		Arrays.stream(lines)
				.map(String::trim)
				.filter(item->item.startsWith("private"))
				.map(item->{
					String[] fieldInfo = StringAnalysisUtil.getFieldInfo(item);
					String fieldName = fieldInfo[2];
					fieldName = fieldName.trim();
					return String.format(".eq(StringUtils.isNotEmpty(req.get%1$s()), %1$s::get%1$s, req.get%1$s())",
							StringAnalysisUtil.head2UpperCase(fieldName), doName);
				}).forEach(System.out::println);
	}


	/**req转动态sql if*/
	@Test
	public void printConditionByReq(){
		String path =
				"D:\\github\\pharmacy-mall\\pharmacy-mall-client\\src\\main\\java\\com\\shp\\tsfservice\\pharmacy\\mall\\client\\model\\req\\lessee\\shop\\LesseePlatReq.java" +
						"";
		String argName = "";
		String code = "";
		{ code="";
		}
		if(StringUtils.isNotBlank(path)){
			code = getClassBody(path);
		}


		String[] lines = code.split("\n");
		System.out.println("WHERE t1.yn = 1\n" +
				"\t\tand t1.lessee_code=${lesseeCode}");
		Arrays.stream(lines)
				.map(String::trim)
				.filter(item->item.startsWith("private"))
				.map(item->{
					String[] fieldInfo = StringAnalysisUtil.getFieldInfo(item);
					String fieldName = fieldInfo[2];
					fieldName = fieldName.trim();
					String columnName = StringAnalysisUtil.back2Subline(fieldName);
					fieldName = StringUtils.isBlank(argName)?fieldName:argName+"."+fieldName;
					String appendCondition = getAppendCondition(fieldInfo[1], fieldName);
					String conditionTemplate = "          AND t1.%2$s = #{%1$s}\r\n";
					if(fieldName.endsWith("Time") || fieldName.endsWith("Date")
							|| fieldName.contains("gmt")){
						// 时间条件，拼接时分秒
						if(fieldName.toLowerCase().contains("start")){
							conditionTemplate = "          AND t1.%2$s &gt;= CONCAT(#{%1$s}, '00:00:00') \r\n";
						}else{
							conditionTemplate = "          AND t1.%2$s &lt;= CONCAT(#{%1$s}, '23:59:59') \r\n";
						}
					}
					return String.format("        <if test='%1$s!=null %3$s'>\r\n" +
							conditionTemplate +
							"        </if>", fieldName, columnName, appendCondition);
				}).forEach(System.out::println);
	}

	@Test
	public void printUpdateAll(){
		String info = "id, lessee_code, name, distribution_type, distribution_channel_type, distribution_range, \n" +
				"    distribution_price, min_delivery_amount, is_free_distribution, free_distribution_shopping_price, app_key,\n" +
				"    app_secret, distribution_service_code, desire_reach_time, desire_receive_time, status, create_time, create_pin,\n" +
				"    update_time, update_pin, yn, distribution_channel_id";
		List<String> excludes = Arrays.asList("create_time","create_pin","update_time","yn");
		String[] columns = info.trim().split("[\\s,]+");
		StringBuilder builder = new StringBuilder("update a t1\nset\n");
		for (int i = 0; i < columns.length; i++) {
			String column = columns[i];
			if(excludes.contains(column)){
				continue;
			}
			String str = String.format(i==columns.length-1? "\tt1.%s=#{%s}\n":"\tt1.%s=#{%s},\n",
					column, StringAnalysisUtil.toCamel(column));
			builder.append(str);
		}
		builder.append("where t1.id=#{id}");
		System.out.println(builder);
	}

	@Test
	public void printInsertList(){
		String info = "id,type";
//		List<String> excludes = Arrays.asList("create_time","create_pin","update_time","yn");
		String[] columns = info.trim().split("[\\s,]+");
		StringBuilder builder = new StringBuilder("insert into a (")
				.append(info)
				.append(")\nvalues (\n")
				.append("<foreach collection=\"list\" item=\"item\" open=\"\" separator=\",\" close=\"\">\n");
		for (int i = 0; i < columns.length; i++) {
			String column = columns[i];
			String str = String.format(i==columns.length-1?"#{item.%s}":"#{item.%s},",
					StringAnalysisUtil.toCamel(column));
			builder.append(str);
		}
		builder.append("\n</foreach>\n)");
		System.out.println(builder);
	}

	@Test// 打印注解内sql
	public void printAnnoSql(){
		String sql = "" +
				"" +
				"";
		Arrays.stream(sql.split("\n"))
				.map(e->e.trim()
						.replaceAll("^\"", "")
						.replaceAll("\"\\s*\\+?$", "")
						.replaceAll(" (TABLE)", " DB.$1")
						.replaceAll("#\\{[\\s\\w,=]+\\}", "'1'"))
				.forEach(System.out::println);
	}

	/**动态sql，条件拼接1。简单输出，explain测试表、字段是否正确*/
	@Test
	public void printSimpleDynamicSql(){
    String originStr =
        "";
		List<String> list = Arrays.asList("<where>", "<set>");
		originStr = originStr.replaceAll("(?i)\\<Where\\>", "where")
				.replaceAll("(?i)\\<set\\>", "set")
				.replaceAll("(?i)\\<foreach[^\\>]+\\>", "(")
				.replaceAll("(?i)\\</foreach\\>", ")")
				.replaceAll("(?i)\\<otherwise\\>\\s+select", " union all select ");
		StringBuilder builder = new StringBuilder();
		Arrays.stream(originStr.split("\r?\n"))
				.filter(StringUtils::isNotBlank)
				.filter(item-> {
					String str = item.trim();
					return (!str.startsWith("<") && !str.endsWith(">"))
							|| list.contains(str.replaceAll("\\s", "").toLowerCase());
				})
				.map(item->item.replaceAll("#\\{[\\w ,\\.=]+\\}", "'1'")
						.replaceAll("\\$\\{[\\w ,\\.=]+\\}", "'1'")
						+"\n")
				.forEach(builder::append);
		System.out.println(builder.toString()
				.replaceAll("(?i)where(\\s+)AND", "where$1")
				.replaceAll("(?i)set(\\s+),", "set$1")
				.replaceAll("&lt;", "<")
				.replaceAll("&gt;", ">"));
	}

	@Test// 按参生成动态sql
	public void printRealDynamicSql() throws FileNotFoundException, SQLException {
		String json =
				"{\n" +
						"  \"id\": 1,\n" +
						"  \"userId\": 12,\n" +
						"  \"lesseeCode\": \"demoData\",\n" +
						"  \"departmentCodes\": [\n" +
						"  ]\n" +
						"}";
		String methodContent =
				"<select id=\"getLesseeShopList\"\n" +
						"          resultType=\"com.shp.tsfservice.pharmacy.mall.client.model.resp.DropDownListResp\">\n" +
						"    select t1.id id,\n" +
						"           t1.code code,\n" +
						"           t1.name name\n" +
						"    from lessee_shop_info t1\n" +
						"    WHERE t1.yn = 1\n" +
						"      <if test='id!=null  and id!=0'>\n" +
						"        AND t1.id = #{id}\n" +
						"      </if>\n" +
						"      <if test='lesseeCode!=null  and lesseeCode!=\"\"'>\n" +
						"        AND t1.lessee_code = #{lesseeCode}\n" +
						"      </if>\n" +
						"      <if test='departmentCodes!=null  and departmentCodes.size()>0'>\n" +
						"        AND t1.lessee_code in\n" +
						"        <foreach collection=\"departmentCodes\" close=\")\" item=\"item\" open=\"(\" separator=\",\">\n" +
						"           #{item}\n" +
						"        </foreach>\n" +
						"      </if>\n" +
						"\n" +
						"  </select>";

		// 获取方法名
		String tempStr = methodContent.trim();
		int start = tempStr.indexOf(" id");
		start = tempStr.indexOf("\"", start);
		String methodName = tempStr.substring(start+1, tempStr.indexOf("\"", start+1));
		// 移除resultType
		methodContent = methodContent.replaceFirst("\\sresultType\\s*=\\s*\"[^\"]+\"", "")
				.replaceFirst("\\sparameterType\\s*=\\s*\"[^\"]+\"", "");

		String mapper = String.format(
				"<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n"
						+ "<!DOCTYPE mapper PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\" \"http://mybatis.org/dtd/mybatis-3-mapper.dtd\" >\n"
						+ "<mapper namespace=\"testDao\">" +
						"%s</mapper>", methodContent);
		// 按条件直接输出sql
		Object paramObject = JSON.parse(json);

		Configuration conf = new Configuration();
		InputStream is = new ByteArrayInputStream(mapper.getBytes(StandardCharsets.UTF_8));
		XMLMapperBuilder builder = new XMLMapperBuilder(is, conf, "", conf.getSqlFragments());
		builder.parse();
		// 计算stmtId，
//		String stmtId = resource.replaceAll("\\\\", "/")
//				.split("/java/")[1]
//				.replaceAll("([/]?xml)", "")
//				.replaceAll("[/]", ".")+methodName;
//		MappedStatement ms =conf.getMappedStatement(stmtId);
		MappedStatement ms = conf.getMappedStatements().stream()
				.filter(item->item.getId().endsWith("."+methodName.trim()))
				.findFirst().orElse(null);
		BoundSql boundSql = ms.getBoundSql(paramObject);
//		System.out.println(boundSql.getSql());// 未替换参数
		String sql = boundSql.getSql();//替换参数
		Configuration configuration = ms.getConfiguration();
		List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
		TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
		if (parameterMappings != null) {
			for (int i = 0; i < parameterMappings.size(); i++) {
				ParameterMapping parameterMapping = parameterMappings.get(i);
				if (parameterMapping.getMode() != ParameterMode.OUT) {
					Object value;
					String propertyName = parameterMapping.getProperty();
					if (boundSql.hasAdditionalParameter(propertyName)) {
						value = boundSql.getAdditionalParameter(propertyName);
					} else if (paramObject == null) {
						value = null;
					} else if (typeHandlerRegistry.hasTypeHandler(paramObject.getClass())) {
						value = paramObject;
					} else {
						MetaObject metaObject = configuration.newMetaObject(paramObject);
						value = metaObject.getValue(propertyName);
					}
					sql = replacePlaceholder(sql, value);
				}
			}
		}
		System.out.println(sql.replaceAll("\n[\\s]+", "\n "));
	}

	@Test// 生成insert语句
	public void genDataByInsert(){
		// set @a=IFNULL(@a+1, 1);  -- 递增本地变量；
		// floor(rand()*10000)；获取整数
		int num = 10;
    String sql =
        "INSERT INTO `shp-mall`.`lessee_shop_info` (`id`, `code`, `lessee_code`, `lessee_department_code`, `name`, `is_control_stock`, `phone`, `qr_url`, `location_province`, `location_city`, `location_district`, `location_details`, `lng`, `lat`, `business_time`, `manage_type`, `min_delivery_amount`, `free_distribution_shopping_price`, `distribution_price`, `distribution_channel_id`, `distribution_range`, `distribution_description`, `status`, `material_status`, `create_time`, `create_pin`, `update_time`, `update_pin`, `yn`) VALUES (3055653044, 'DP渝中区050001', 'HPYF', '渝中区05', '和平药房05', 0, '13323455432', NULL, '重庆市', '重庆市', '渝中区', '沧白路23号', 87.64, 7.58, '[{\\\"dayOfWeek\\\":1,\\\"endTime\\\":\\\"demoData\\\",\\\"startTime\\\":\\\"demoData\\\"}]', 'O2O', 0.00, 0.00, 0.00, NULL, NULL, NULL, '-1', 'FINISH_BASIC_INFO', '2023-08-11 11:15:51', '1', '2023-08-11 11:15:51', '1', 1);\n" +
				"";
		Map<String, String> map = MapUtil.builder("id", "rand()*10000000")
				/**模板*/
				// 连接
				.put("concat", "concat('', @a)")
				.put("concat", "concat('', floor(rand()*10000))")
				// 枚举；order、repeat必须,结尾
				.put("ab", "if(rand()>0.5,'a','b')  ")
				.put("abcdOrder", "SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c,d,', ',', (@a%4+1)), ',', -1)")
				.put("abcRepeat", "SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c,', ',', (floor(@a/4)%3+1)), ',', -1)")
				// 日期
				.put("date", "date_add('2023-11-01', interval floor(@a/8) DAY")
				/**填充*/
				.put("", "")
				.put("", "")

				/**默认值*/
				.put("create_time", "CURRENT_TIMESTAMP()")
				.put("update_time", "CURRENT_TIMESTAMP()")
				.build();

		sql = sql.trim();
		int firstBraket = sql.indexOf("(");
		String pre = sql.substring(0, firstBraket);
		String[] split = sql.substring(firstBraket).split("VALUES");
		String[] fields = split[0].trim()
				.replaceAll("((^\\()|(\\)$))", "")
				.split(", ");
		String[] values = split[1].trim()
				.replaceAll(";$", "")
				.replaceAll("((^\\()|(\\)$))", "")
				.split(", ");
		if(fields.length!=values.length){
			throw new RuntimeException("属性、值数量不对应");
		}
		for (int i = 0; i< values.length; i++) {
			String field = fields[i].replaceAll("[\\s`'\"]+", "");
			if(map.containsKey(field)){
				values[i] = map.get(field);
			}
		}
		split[1] = Arrays.stream(values).collect(Collectors.joining(", "));

		String insertSql = String.format("%s %s VALUES (%s);", pre, split[0], split[1]);
		System.out.println("set @a=0;  -- 递增本地变量；");
		for (int i = 0; i < num; i++) {
			System.out.println("set @a=IFNULL(@a+1, 1);  -- 递增本地变量；");
			System.out.println(insertSql);
		}
	}

	/**日志信息替换参数，打印sql*/
	@Test
	public void printSqlByLog() {
		String info = ": ==>  Preparing: SELECT count(0) FROM qft_room_cleanup r LEFT JOIN qft_focus_housing housing ON r.house_id = housing.id AND r.room_type = 1 LEFT JOIN qft_focus_room room ON room.id = r.room_id AND r.room_type = 1 LEFT JOIN qft_whole_housing wholeHousing ON r.house_id = wholeHousing.id AND r.room_type = 2 LEFT JOIN qft_whole_room wholeRoom ON wholeRoom.id = r.room_id AND r.room_type = 2 LEFT JOIN qft_joint_housing jointHousing ON r.house_id = jointHousing.id AND r.room_type = 3 LEFT JOIN qft_joint_room jointRoom ON jointRoom.id = r.room_id AND r.room_type = 3 WHERE r.is_delete = 0 AND r.transfer_status IN (0, 2) AND r.cleanup_source = 3 AND r.company_id = ? AND r.store_id IN (?, ?, ?, ?, ?) \r\n" + 
				"2020-10-10 15:18:37.835 -- [9cd0837d-b1f8-4cdd-94e6-561bf07e66fd] --143 DEBUG 19216 --- [:20028-thread-2] c.b.m.w.W.getPCWechatCleanup_COUNT       : ==> Parameters: 96(Long), 496(Long), 657(Long), 1078(Long), 392(Long), 492(Long)\r\n" + 
				"2020-10-10 15:18:37.835 -- [9cd0837d-b1f8-4cdd-94e6-561bf07e66fd] -- : ==> Para";
		String[] infos = info.split("\r\n");
		String sql = infos[0].split(" Preparing: ")[1];
		String paramInfo=infos[1].split(" Parameters: ")[1];
		paramInfo = paramInfo.replaceAll("\\([^\\)]*\\)", "");//
		String[] params = paramInfo.split(",");
//		System.out.println(sql);
//		System.out.println(params);
		for (int i = 0; i < params.length; i++) {
			String p = params[i].trim();
			p = p.matches("\\d+") ? p : "'"+p.trim()+"'";
			sql = sql.replaceFirst(Pattern.quote("?"), p);
		}
		System.out.println(sql);
	}

	/**if 附加判定*/
	private String getAppendCondition(String fieldType, String fieldName){
		fieldType = fieldType.trim();
		String appendCondition = "";
		List<String> numberTypeList = Arrays.asList("byte", "short", "int", "long"
				, "Byte", "Short", "Integer", "Long"
				, "float", "double", "Float", "Double");
		String template = " and %s!=%s";
		if(numberTypeList.contains(fieldType)){
			appendCondition = String.format(template, fieldName, "0");
		}else if("String".equals(fieldType)){
			appendCondition = String.format(template, fieldName, "\"\"");
		}
		return appendCondition;
	}



	private static final DateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	private String replacePlaceholder(String sql, Object propertyValue) {
	      String result;
	      if (propertyValue != null) {
	         if (propertyValue instanceof String) {
	            result = "'" + propertyValue + "'";
	         } else if (propertyValue instanceof Date) {
	            result = "'" + DATE_FORMAT.format(propertyValue) + "'";
	         } else {
	            result = propertyValue.toString();
	         }
	      } else {
	         result = "null";
	      }
	      return sql.replaceFirst("\\?", Matcher.quoteReplacement(result));
	}

	private String getClassBody(String path){
		String content = "";
		try {
			List<String> list = Files.readAllLines(Paths.get(path));
			for (int i = 0; i < list.size(); i++) {
				if(list.get(i).contains("public class")){
					list = list.subList(i, list.size());
					break;
				}
			}
			content = list
					.stream()
					.filter(item->StringUtils.isNotBlank(item) && !item.contains(" static "))
					.collect(Collectors.joining("\n"));
		} catch (IOException e) {
			e.printStackTrace();
		}
		return content;
	}


}
